package com.oa.lms.apps.dao.user.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.lms.apps.dao.user.IUsersDao;

import com.oa.lms.apps.entity.user.UserInfoValue;
import com.oa.lms.apps.entity.user.UsersValue;

public class UsersDaoImpl extends HibernateDaoSupport implements IUsersDao {

	List<UsersValue> list;
	
	private Integer totalNumber;
	
	@Override
	public Integer add(UsersValue usersValue) {
		if (usersValue != null) {
			this.getHibernateTemplate().save(usersValue);
		}
		return usersValue.getUserId();
	}

	@Override
	public void modify(UsersValue usersValue) {
		if (usersValue != null) {
			this.getHibernateTemplate().update(usersValue);
		}

	}
	@Override
	public void delete(UsersValue usersValue) {
		if (usersValue != null) {
			this.getHibernateTemplate().delete(usersValue);
		}
	}

	@Override
	public UsersValue queryByUserId(Integer userId) {
		if (userId != null) {
			return (UsersValue) this.getHibernateTemplate().get(UsersValue.class, userId);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UsersValue> queryUsers(final UsersValue usersValue, final UserInfoValue userInfoValue,final Integer rows, final Integer pageNo) {
		String hqlStr = "SELECT uv FROM UsersValue uv, UserInfoValue uiv WHERE 1=1 AND uv.userInfo.infoId = uiv.infoId";
		if (usersValue.getUserName() != null) {
			hqlStr += " AND uv.userName = ?";
		}
		if (usersValue.getPassword() != null){
			hqlStr += " AND uv.password = ?";
		}
		if(userInfoValue != null){
			if (userInfoValue.getPhone() != null) {
				hqlStr += " AND uiv.phone = ?";
			}
			if (userInfoValue.getMailInfo() != null) {
				hqlStr += " AND uiv.mailInfo = ?";
			}
			if (userInfoValue.getPostInfo() != null) {
				hqlStr += " AND uiv.postInfo = ?";
			}
		}
		hqlStr += " AND uv.status <> -1";
		final String hql = hqlStr;
		
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (usersValue.getUserName() != null) {
					query.setString(++index, usersValue.getUserName());
				}
				if (usersValue.getPassword() != null){
					query.setString(++index, usersValue.getPassword());
				}
				if(userInfoValue != null){
					if (userInfoValue.getPhone() != null) {
						query.setString(++index, userInfoValue.getPhone());
					}
					if (userInfoValue.getMailInfo() != null) {
						query.setEntity(++index, userInfoValue.getMailInfo());
					}
					if (userInfoValue.getPostInfo() != null) {
						query.setEntity(++index, userInfoValue.getPostInfo());
					}
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public Integer getTotalPageNumber(final UsersValue usersValue, final UserInfoValue userInfoValue,Integer rows){
		int totalPageNumber;
		String hqlStr = "SELECT uv FROM UsersValue uv, UserInfoValue uiv WHERE 1=1 AND uv.userInfo.infoId = uiv.infoId";
		if (usersValue.getUserName() != null) {
			hqlStr += " AND uv.userName = ?";
		}
		if (usersValue.getPassword() != null){
			hqlStr += " AND uv.password = ?";
		}
		if(userInfoValue != null){
			if (userInfoValue.getPhone() != null) {
				hqlStr += " AND uiv.phone = ?";
			}
			if (userInfoValue.getMailInfo() != null) {
				hqlStr += " AND uiv.mailInfo = ?";
			}
			if (userInfoValue.getPostInfo() != null) {
				hqlStr += " AND uiv.postInfo = ?";
			}
		}
		hqlStr += " AND uv.status <> -1";
		final String hql = hqlStr;
		
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (usersValue.getUserName() != null) {
					query.setString(++index, usersValue.getUserName());
				}
				if (usersValue.getPassword() != null){
					query.setString(++index, usersValue.getPassword());
				}
				if(userInfoValue != null){
					if (userInfoValue.getPhone() != null) {
						query.setString(++index, userInfoValue.getPhone());
					}
					if (userInfoValue.getMailInfo() != null) {
						query.setEntity(++index, userInfoValue.getMailInfo());
					}
					if (userInfoValue.getPostInfo() != null) {
						query.setEntity(++index, userInfoValue.getPostInfo());
					}
				}
				list = query.list();
				return list;
			}
		});
		totalNumber = list.size();
		if (totalNumber <= rows) {
			totalPageNumber = 1;
		} else {
			if (totalNumber % rows == 0) {
				totalPageNumber = totalNumber / rows;
			} else {
				totalPageNumber = totalNumber / rows + 1;
			}
		}
		return totalPageNumber;
		
	}

	public List<UsersValue> getList() {
		return list;
	}

	public void setList(List<UsersValue> list) {
		this.list = list;
	}

	public Integer getTotalNumber() {
		return totalNumber;
	}

	public void setTotalNumber(Integer totalNumber) {
		this.totalNumber = totalNumber;
	}
	
	

}
